package com.moarea.app.service.impl.querys;

import com.moarea.app.dao.BaseCommonqueryMapper;
import com.moarea.app.dao.BaseCommonqueryconditionMapper;
import com.moarea.app.dao.BaseCommonqueryresultMapper;
import com.moarea.app.dao.BaseDictionaryMapper;
import com.moarea.app.lang.model.ResultObject;
import com.moarea.app.lang.utils.StringUtils;
import com.moarea.app.model.BaseCommonquery;
import com.moarea.app.model.BaseCommonqueryData;
import com.moarea.app.model.BaseCommonquerycondition;
import com.moarea.app.model.BaseCommonqueryresult;
import com.moarea.app.model.BaseDictionary;
import com.moarea.app.model.LoginUser;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.google.gson.Gson;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.moarea.app.mybatis.model.PagedObject;
import com.moarea.app.mybatis.service.BaseService;
import com.moarea.app.resource.ResourceRepository;
import com.moarea.app.util.MapTrimUtil;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Service;
import org.springframework.web.context.support.WebApplicationContextUtils;
import tk.mybatis.mapper.common.Mapper;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by 周树权 on 2017/4/10.
 */

@Service("BaseCommonqueryService")
public class BaseCommonqueryService extends BaseService<BaseCommonquery> {
    @Autowired
    private BaseCommonqueryMapper baseCommonqueryMapper;
    @Autowired
    private BaseCommonqueryconditionMapper baseCommonqueryconditionMapper;
    @Autowired
    private BaseCommonqueryresultMapper baseCommonqueryresultMapper;
    @Autowired
    private BaseDictionaryMapper baseDictionaryMapper;

    @Override
    public Mapper<BaseCommonquery> getMapper() {
        return baseCommonqueryMapper;
    }

    /**
     * 获取出所有的合同模版类型及相关的模版
     *
     * @param session HttpSession对象
     * @return 菜单数据集合
     */
    public ResultObject listCommonQueryTypes(HttpSession session) {

        //最终结果集合
        List<Map<String, Object>> returnMap = new LinkedList<>();
        Map<String, Object> rootMap = new HashMap<>();
        rootMap.put("id", "0");
        rootMap.put("name", "通用查询配置维护");
        rootMap.put("pId", "rootNode");
        rootMap.put("open", true);
        returnMap.add(rootMap);

        //先从字典表中查询出合同模版的类型
        List<BaseCommonquery> basecommonquerys = baseCommonqueryMapper.selectNodeByParentid("0");

        //然后遍历字典表集合，用每一个字典表数据去查询下面挂载的子菜单
        for (BaseCommonquery baseDictionary : basecommonquerys) {

            //先把每一项字典表节点作为树的节点保存下拉
            Map<String, Object> baseDictionaryNode = new HashMap<>();
            baseDictionaryNode.put("id", baseDictionary.getID());
            baseDictionaryNode.put("name", baseDictionary.getNAME());
            baseDictionaryNode.put("pId", baseDictionary.getPARENTID());
            baseDictionaryNode.put("open", true);

            returnMap.add(baseDictionaryNode);
            returnMap.addAll(baseCommonqueryMapper.listCommonQueryTypes(baseDictionary.getID().toString()));
        }

        return ResultObject.createInstance(true, "", returnMap);
    }

    /**
     * 保存模板信息
     *
     * @param baseCommonqueryData
     * @param session
     * @return
     */
    public ResultObject save(BaseCommonqueryData baseCommonqueryData, HttpSession session) {
        ResultObject resultObject = ResultObject.createInstance();

        if (baseCommonqueryData != null) {

            BaseCommonquery commonquery = baseCommonqueryData.getBaseCommonquery();

            List<BaseCommonquerycondition> baseCommonqueryconditions = baseCommonqueryData.getBaseCommonqueryconditions();

            List<BaseCommonqueryresult> baseCommonqueryresults = baseCommonqueryData.getBaseCommonqueryresults();
            commonquery.setSTATE("01");
            baseCommonqueryMapper.insert(commonquery);

            commonquery = baseCommonqueryMapper.selectByCode(commonquery.getCODE());
            String parentid = commonquery.getID() + "";
            if (baseCommonqueryconditions != null && baseCommonqueryconditions.size() > 0) {
                int i = 0;
                for (BaseCommonquerycondition commonquerycondition : baseCommonqueryconditions) {
                    if (StringUtils.isNotBlank(commonquerycondition.getFIELDNAME())) {
                        commonquerycondition.setPARENTID(parentid);
                        String id = UUID.randomUUID().toString();
                        commonquerycondition.setID(id);
                        commonquerycondition.setSEQUENCE(i);
                        i++;
                        baseCommonqueryconditionMapper.insert(commonquerycondition);
                    }
                }

            }
            if (baseCommonqueryresults != null && baseCommonqueryresults.size() > 0) {
                int i = 0;
                for (BaseCommonqueryresult baseCommonqueryresult : baseCommonqueryresults) {
                    if (StringUtils.isNotBlank(baseCommonqueryresult.getNAME1())) {
                        baseCommonqueryresult.setPARENTID(parentid);
                        String id = UUID.randomUUID().toString();
                        baseCommonqueryresult.setID(id);
                        baseCommonqueryresult.setSEQUENCE(i);
                        i++;
                        baseCommonqueryresultMapper.insert(baseCommonqueryresult);
                    }
                }
            }


            resultObject.setData(commonquery);
        }
        return resultObject;
    }

    /**
     * 根据ID获取表头信息
     *
     * @param baseCommonquery
     * @param session
     * @return
     */
    public ResultObject getEntityById(BaseCommonquery baseCommonquery, HttpSession session) {
        ResultObject resultObject = ResultObject.createInstance();
        baseCommonquery = baseCommonqueryMapper.selectOne(baseCommonquery);
        resultObject.setData(baseCommonquery);
        resultObject.setSuccess(true);
        return resultObject;
    }

    /**
     * 更新模板信息
     *
     * @param baseCommonqueryData
     * @param session
     * @return
     */
    public ResultObject update(BaseCommonqueryData baseCommonqueryData, HttpSession session) {
        ResultObject resultObject = ResultObject.createInstance();
        if (baseCommonqueryData != null) {
            BaseCommonquery commonquery = baseCommonqueryData.getBaseCommonquery();
            List<BaseCommonquerycondition> baseCommonqueryconditions = baseCommonqueryData.getBaseCommonqueryconditions();
            List<BaseCommonqueryresult> baseCommonqueryresults = baseCommonqueryData.getBaseCommonqueryresults();
            baseCommonqueryMapper.updateByPrimaryKeySelective(commonquery);
            String parentid = commonquery.getID() + "";
            if (baseCommonqueryconditions != null && baseCommonqueryconditions.size() > 0) {
                for (BaseCommonquerycondition commonquerycondition : baseCommonqueryconditions) {
                    if (StringUtils.isBlank(commonquerycondition.getID())) {
                        if (StringUtils.isNotBlank(commonquerycondition.getFIELDNAME())) {
                            commonquerycondition.setPARENTID(parentid);
                            String id = UUID.randomUUID().toString();
                            commonquerycondition.setID(id);
                            baseCommonqueryconditionMapper.insert(commonquerycondition);
                        }
                    } else {
                        baseCommonqueryconditionMapper.updateByPrimaryKeySelective(commonquerycondition);
                    }
                }

            }
            if (baseCommonqueryresults != null && baseCommonqueryresults.size() > 0) {
                for (BaseCommonqueryresult baseCommonqueryresult : baseCommonqueryresults) {
                    if (StringUtils.isBlank(baseCommonqueryresult.getID())) {
                        if (StringUtils.isNotBlank(baseCommonqueryresult.getNAME1())) {
                            baseCommonqueryresult.setPARENTID(parentid);
                            String id = UUID.randomUUID().toString();
                            baseCommonqueryresult.setID(id);
                            baseCommonqueryresultMapper.insert(baseCommonqueryresult);
                        }
                    } else {
                        baseCommonqueryresultMapper.updateByPrimaryKeySelective(baseCommonqueryresult);
                    }
                }
            }
        }
        return resultObject;
    }


    /**
     * 根据表头的ID获取 结果信息
     *
     * @param param
     * @param pageNum
     * @param pageSize
     * @param count
     * @return
     */
    public PagedObject<List<Map<String, Object>>> listCommonQueryResults(Map<String, Object> param, int pageNum, int pageSize, int count, HttpSession session) {
        //去除参数的空格
        MapTrimUtil.MapValueTrim(param);

        Page<Map<String, Object>> page = PageHelper.startPage(pageNum, pageSize, count < 0);

        List<Map<String, Object>> list = baseCommonqueryresultMapper.selectAllEnter(param);
        if (list == null) {
            list = new ArrayList<>();
        }
        Map<String, Object> map = new HashMap<>();
        map.put("COLWIDTH", "100");
        list.add(map);


        PagedObject<List<Map<String, Object>>> pagedObject = new PagedObject<List<Map<String, Object>>>(list, count < 0 ? (int) page.getTotal() : count);

        return pagedObject;
    }

    /**
     * 根据表头的ID获取查询信息
     *
     * @param param
     * @param pageNum
     * @param pageSize
     * @param count
     * @param session
     * @return
     */
    public PagedObject<List<Map<String, Object>>> listCommonQueryConditions(Map<String, Object> param, int pageNum, int pageSize, int count, HttpSession session) {
        //去除参数的空格
        MapTrimUtil.MapValueTrim(param);

        Page<Map<String, Object>> page = PageHelper.startPage(pageNum, pageSize, count < 0);

        List<Map<String, Object>> list = baseCommonqueryconditionMapper.selectAllEnter(param);
        if (list == null) {
            list = new ArrayList<>();
        }
        Map<String, Object> map = new HashMap<>();
        map.put("COMPONENTNAME", "1");
        map.put("INITVALUE", "0");
        map.put("COLUMNWIDTH", "200");
        map.put("WIDTH", "0.5");
        list.add(map);
        PagedObject<List<Map<String, Object>>> pagedObject = new PagedObject<List<Map<String, Object>>>(list, count < 0 ? (int) page.getTotal() : count);

        return pagedObject;
    }


    /**
     * 模板通用查询方法
     *
     * @param param
     * @param pageNum
     * @param pageSize
     * @param count
     * @param sortName
     * @param sortOrder
     * @param session
     * @return
     */
    public PagedObject<List<Map<String, Object>>> selectAllDataByTemplete(Map<String, Object> param, int pageNum, int pageSize, int count, HttpSession session) {
        MapTrimUtil.MapValueTrim(param);
        //如果sql为空，返回null
        if (StringUtils.isBlank(param, "sql")) {
            return null;
        }
        String sql = param.get("sql").toString();
        sql=sql.replaceAll(";","");
        LoginUser loginUser = (LoginUser) session.getAttribute(ResourceRepository.SESSION_LOGIN_USER);
        if (loginUser == null) {
            loginUser = new LoginUser();
        }
        sql=replaceByParam(sql,loginUser);
        String codeId = param.get("codeId").toString();
        List<Map<String, Object>> mapList = baseCommonqueryMapper.getConditionsByCode(codeId);
        if (!(mapList != null && mapList.size() > 0)) {
            return null;
        }
        List<Map<String, Object>> resultList = baseCommonqueryMapper.getResultsByCode(codeId);
        if (!(resultList != null && resultList.size() > 0)) {
            return null;
        }
        if (mapList != null) {
            for (Map<String, Object> map : mapList) {
                if (StringUtils.isNotBlank(map, "REPLACEFIELD")) {
                    String replacefield = map.get("REPLACEFIELD").toString();
                    boolean dataFlag = false;//用于判断是不是日期输入框
                    if (StringUtils.isNotBlank(map, "COMPONENTNAME") && ("5".equals(map.get("COMPONENTNAME")) || "6".equals(map.get("COMPONENTNAME")))) {
                        dataFlag = true;
                    }
                    if (StringUtils.isNotBlank(param, replacefield)) {
                        String str = param.get(replacefield).toString();
                        sql = sql.replace(replacefield, str);
                    } else {
                        if (dataFlag) {
                            String reverseSql = new StringBuilder(sql).reverse().toString();
                            String reverseCurr = new StringBuilder(replacefield).reverse().toString();
                            int currIndex = reverseSql.indexOf(reverseCurr);
                            String upCaseSql = reverseSql.toUpperCase();
                            int whereIndex = upCaseSql.indexOf("EREHW", currIndex);
                            int firstAndIndex = upCaseSql.indexOf("DNA", currIndex);
                            if (firstAndIndex == -1 || firstAndIndex > whereIndex) {
                                //替换where 和替换符之间的字符串
                                String restr = reverseSql.substring(currIndex +1+ reverseCurr.length(), whereIndex);
                                reverseSql = reverseSql.replace(reverseCurr, "1");
                                reverseSql = reverseSql.replace(restr, "=1 ");
                            } else {
                                //替换and 和替换符之间的字符串
                                String restr = reverseSql.substring(currIndex +1+ reverseCurr.length(), firstAndIndex);
                                reverseSql = reverseSql.replace(reverseCurr, "1");
                                reverseSql = reverseSql.replace(restr, "=1 ");
                            }
                            sql = new StringBuilder(reverseSql).reverse().toString();
                        } else {
                            sql = sql.replace(replacefield, "");
                        }
                    }
                }
            }
        }
        String[] splitSql = sql.split("@SPLITSQL");
        String callSql="";
        String selectSql=sql;
        if(splitSql!=null&&splitSql.length>1){
            callSql=splitSql[0];
            selectSql=splitSql[1];
        }
        String countSql = "select count(*) from (" + selectSql + ") A";
        String limitSql = selectSql + " limit " + (pageNum - 1) * pageSize + "," + pageSize;
        List<Map<String, Object>> list = new ArrayList<>();
        Connection connection = getConnection(session);
        if(StringUtils.isNotBlank(callSql)){
            if(!callStatement(connection,callSql)){
                //如果存储过程调用失败则直接返回空数字
                return new PagedObject<List<Map<String, Object>>>(list,  count);
            }
        }
        Statement state = null;
        Statement state1 = null;
        try {
            state = connection.createStatement();
            state1 = connection.createStatement();
            ResultSet coutSet = state1.executeQuery(countSql);
            while (coutSet.next()) {
                count = coutSet.getInt(1);
            }
            ResultSet resultSet = state.executeQuery(limitSql);

            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < resultList.size(); i++) {
                    map.put("field" + i, resultSet.getObject(i + 1));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (state != null) {
                try {
                    state.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        PagedObject<List<Map<String, Object>>> pagedObject = new PagedObject<List<Map<String, Object>>>(list,count);
        return pagedObject;

    }
    /**
     * 执行存储过程
     * @param connection
     * @param sql
     */
    public boolean callStatement(Connection connection,String sql) {
        if (StringUtils.isBlank(sql)) {
            return false;
        }
        CallableStatement cs=null;
        String prepStatement = sql;
        try {
            cs = connection.prepareCall(prepStatement);
            cs.execute();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        } finally {
            if(cs!=null){
                try {
                    cs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return true;

    }


    /**
     * 根据sql语句获取下拉框选项
     *
     * @param param
     * @return
     */
    public ResultObject getSelectOption(Map<String, Object> param, HttpSession session) {
        ResultObject resultObject = ResultObject.createInstance(false);
        if (StringUtils.isNotBlank(param, "sql")) {
            String sql = param.get("sql").toString();
            Connection connection = getConnection(session);
            Statement state = null;
            try {
                state = connection.createStatement();
                List<Map<String, Object>> dataList = new ArrayList<>();
                ResultSet resultSet = state.executeQuery(sql);
                while (resultSet.next()) {
                    Object id = resultSet.getObject(1);
                    Object word = resultSet.getObject(2);
                    Map<String, Object> map = new HashMap<>();
                    map.put("id", id);
                    map.put("word", word);
                    dataList.add(map);
                }
                resultObject.setSuccess(true);
                resultObject.setData(dataList);
            } catch (SQLException e) {
                e.printStackTrace();
                resultObject.setData(null);
                resultObject.setSuccess(false);
            } finally {
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (state != null) {
                    try {
                        state.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return resultObject;
    }


    /**
     * 根据页面配置的url参数获取模板信息
     *
     * @param param
     * @param session
     * @return
     */
    public ResultObject listCommonQueryTempleteData(Map<String, Object> param, HttpSession session) {
        ResultObject resultObject = ResultObject.createInstance(false);
        Map<String, Object> dataMap = new HashMap<>();
        if (StringUtils.isNotBlank(param, "codeId")) {
            String code = param.get("codeId").toString();
            BaseCommonquery baseCommonquery = baseCommonqueryMapper.selectByCode(code);
            if (baseCommonquery == null) {
                resultObject.setSuccess(false);
                return resultObject;
            }
            List<Map<String, Object>> conditionList = baseCommonqueryMapper.getConditionsByCode(code);
            List<Map<String, Object>> resultList = baseCommonqueryMapper.getResultsByCode(code);
            Integer isGroup=baseCommonqueryMapper.isGroup(baseCommonquery.getID().toString());
            dataMap.put("head", baseCommonquery);
            dataMap.put("conditions", conditionList);
            dataMap.put("results", resultList);
            dataMap.put("isGroup",isGroup!=null&&isGroup>0?true:false);
            resultObject.setSuccess(true);
            resultObject.setMessage("成功");
            resultObject.setData(dataMap);
        }
        return resultObject;
    }

    /**
     * 从配置文件中获取数据库连接串
     *
     * @return
     * @param]
     */
    public Connection getConnection(HttpSession session) {

        ServletContext servletContext = session.getServletContext();
        ApplicationContext ac = WebApplicationContextUtils.getWebApplicationContext(servletContext);
        //获取datasource  Bean
        ComboPooledDataSource comboPooledDataSource = ac.getBean("dataSource", ComboPooledDataSource.class);
        Connection conn = null;
        try {
            conn = comboPooledDataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 根据父ID获取所有的子节点
     *
     * @param param
     * @param pageNum
     * @param pageSize
     * @param count
     * @param sortName
     * @param sortOrder
     * @param session
     * @return
     */
    public PagedObject<List<BaseCommonquery>> selectChildBypId(Map<String, Object> param, int pageNum, int pageSize, int count, HttpSession session) {
        //去除参数的空格
        MapTrimUtil.MapValueTrim(param);
        List<BaseCommonquery> list = new ArrayList<>();
        Page<BaseCommonquery> page = PageHelper.startPage(pageNum, pageSize, count < 0);
        if (StringUtils.isNotBlank(param, "id")) {
            list = baseCommonqueryMapper.selectNodeByParentid(param.get("id").toString());
        }
        PagedObject<List<BaseCommonquery>> pagedObject = new PagedObject<List<BaseCommonquery>>(list, count < 0 ? (int) page.getTotal() : count);

        return pagedObject;
    }

    /**
     * 树节点排序功能，及更新操作
     *
     * @param baseCommonquerys
     * @param session
     * @return
     */
    public ResultObject updateList(List<BaseCommonquery> baseCommonquerys, HttpSession session) {
        ResultObject resultObject = ResultObject.createInstance();
        if (baseCommonquerys != null && baseCommonquerys.size() > 0) {
            for (BaseCommonquery baseCommonquery : baseCommonquerys) {
                baseCommonqueryMapper.updateByPrimaryKeySelective(baseCommonquery);
            }
        }
        return resultObject;
    }

    /**
     * 根据表头的Id获取所有的结果集合
     *
     * @param param
     * @param pageNum
     * @param pageSize
     * @param count
     * @param sortName
     * @param sortOrder
     * @param session
     * @return
     */
    public PagedObject<List<Map<String, Object>>> selectResultBypId(Map<String, Object> param, int pageNum, int pageSize, int count,  HttpSession session) {
        //去除参数的空格
        MapTrimUtil.MapValueTrim(param);
        Page<Map<String, Object>> page = PageHelper.startPage(pageNum, pageSize, count < 0);
        List<Map<String, Object>> list = baseCommonqueryresultMapper.selectAllEnter(param);
        PagedObject<List<Map<String, Object>>> pagedObject = new PagedObject<List<Map<String, Object>>>(list, count < 0 ? (int) page.getTotal() : count);

        return pagedObject;
    }

    /**
     * 结果排序，及结果表批量更新
     *
     * @param baseCommonqueryresults
     * @param session
     * @return
     */
    public ResultObject updateResultList(List<BaseCommonqueryresult> baseCommonqueryresults, HttpSession session) {
        ResultObject resultObject = ResultObject.createInstance();
        if (baseCommonqueryresults != null && baseCommonqueryresults.size() > 0) {
            for (BaseCommonqueryresult baseCommonqueryresult : baseCommonqueryresults) {
                baseCommonqueryresultMapper.updateByPrimaryKeySelective(baseCommonqueryresult);
            }
        }
        return resultObject;
    }

    /**
     * 根据表头的Id获取所有的条件集合
     *
     * @param param
     * @param pageNum
     * @param pageSize
     * @param count
     * @param sortName
     * @param sortOrder
     * @param session
     * @return
     */
    public PagedObject<List<Map<String, Object>>> selectConditionBypId(Map<String, Object> param, int pageNum, int pageSize, int count, HttpSession session) {
        //去除参数的空格
        MapTrimUtil.MapValueTrim(param);
        Page<Map<String, Object>> page = PageHelper.startPage(pageNum, pageSize, count < 0);
        List<Map<String, Object>> list = baseCommonqueryconditionMapper.selectAllEnter(param);
        PagedObject<List<Map<String, Object>>> pagedObject = new PagedObject<List<Map<String, Object>>>(list, count < 0 ? (int) page.getTotal() : count);

        return pagedObject;
    }

    /**
     * 条件排序，及条件表批量更新
     *
     * @param baseCommonqueryconditions
     * @param session
     * @return
     */
    public ResultObject updateConditiontList(List<BaseCommonquerycondition> baseCommonqueryconditions, HttpSession session) {
        ResultObject resultObject = ResultObject.createInstance();
        if (baseCommonqueryconditions != null && baseCommonqueryconditions.size() > 0) {
            for (BaseCommonquerycondition baseCommonquerycondition : baseCommonqueryconditions) {
                baseCommonqueryconditionMapper.updateByPrimaryKeySelective(baseCommonquerycondition);
            }
        }
        return resultObject;
    }

    /**
     * 树节点的复制功能
     *
     * @param param
     * @param session
     * @return
     */
    public ResultObject copy(Map<String, Object> param, HttpSession session) {
        ResultObject resultObject = ResultObject.createInstance();
        if (StringUtils.isNotBlank(param, "targetId")) {
            String targetId = param.get("targetId").toString();
            Integer count = baseCommonqueryMapper.selectCountByCode(targetId, null);
            if (count != null && count > 0) {
                resultObject.setMessage("目标查询编码已存在");
                resultObject.setSuccess(false);
            } else {
                if (StringUtils.isNotBlank(param, "treeNodeId")) {
                    BaseCommonquery baseCommonquery = new BaseCommonquery();
                    Integer treeNodeId = Integer.parseInt(param.get("treeNodeId").toString());
                    baseCommonquery.setID(treeNodeId);
                    baseCommonquery = baseCommonqueryMapper.selectByPrimaryKey(baseCommonquery);
                    baseCommonquery.setID(null);
                    baseCommonquery.setCODE(targetId);
                    baseCommonqueryMapper.insert(baseCommonquery);
                    baseCommonquery = baseCommonqueryMapper.selectByCode(targetId);
                    String targetParentId = baseCommonquery.getID().toString();
                    List<BaseCommonquerycondition> conditions = baseCommonqueryconditionMapper.selectByParentId(param);
                    if (conditions != null && conditions.size() > 0) {
                        for (BaseCommonquerycondition baseCommonquerycondition : conditions) {
                            String id = System.currentTimeMillis() + "";
                            baseCommonquerycondition.setID(id);
                            baseCommonquerycondition.setPARENTID(targetParentId);
                            baseCommonqueryconditionMapper.insert(baseCommonquerycondition);
                        }
                    }
                    List<BaseCommonqueryresult> results = baseCommonqueryresultMapper.selectByParentId(param);
                    if (results != null && results.size() > 0) {
                        for (BaseCommonqueryresult baseCommonqueryresult : results) {
                            String id = System.currentTimeMillis() + "";
                            baseCommonqueryresult.setID(id);
                            baseCommonqueryresult.setPARENTID(targetParentId);
                            baseCommonqueryresultMapper.insert(baseCommonqueryresult);
                        }
                    }
                    resultObject.setSuccess(true);
                    resultObject.setMessage("复制成功");
                } else {
                    resultObject.setMessage("请先选择树节点");
                    resultObject.setSuccess(false);
                }
            }
        } else {
            resultObject.setMessage("目标查询编码为空");
            resultObject.setSuccess(false);
        }
        return resultObject;
    }

    /**
     * 导出功能的实现
     *
     * @param session
     * @return
     */
    public ResultObject export(Map<String, Object> param, HttpServletRequest request, HttpServletResponse response, HttpSession session) {

        ResultObject resultObject = ResultObject.createInstance();
        String codeId = "";
        if (StringUtils.isBlank(param, "codeId")) {
            resultObject.setSuccess(false);
            return resultObject;
        } else {
            codeId = param.get("codeId").toString();
        }
        String uuid_file_name = UUID.randomUUID() + ".xls";

        //模板绝对根路径
        String temp_root_path = request.getSession().getServletContext().getRealPath("/") + "Resources\\static\\entem\\currencyTemplete\\";
        //临时文件绝对根路径
        String tmp_root_path = request.getSession().getServletContext().getRealPath("/") + "Resources\\static\\entem\\currencyTemplete\\currencyTempleteFile\\";

        //生成的临时文件完整路径
        String uuid_file_path = (tmp_root_path + uuid_file_name).replaceAll("\\\\", "/");
        String full_file_path = (temp_root_path + "currencyTemplete.xls").replaceAll("\\\\", "/");
        Workbook workbook = null;
        FileInputStream inputStream = null;
        FileOutputStream outputStream = null;

        try {

            //将模板文件读入流文件
            inputStream = new FileInputStream(new File(full_file_path));
            //将要生成的临时文件放入输出流
            outputStream = new FileOutputStream(new File(uuid_file_path));
            //构建Excel文件读写流
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            //构建Excel操作对象
            workbook = new HSSFWorkbook(poifsFileSystem);
            if (workbook != null) {
                Sheet sheet = workbook.getSheetAt(0);
                if (sheet != null) {
                    //根据前台传入的查询条件查出所有的数据
                    List<Map<String, Object>> exportDataList = getAllDataByCondition(param, session);
                    //根据查询编码获取所有的结果集
                    List<BaseCommonqueryresult> commonqueryresults = getResultList(codeId);
                    Row nameRow = sheet.createRow(0);
                    //生成表头行
                    if (nameRow != null) {
                        for (int j = 0; j < commonqueryresults.size(); j++) {
                            Cell cell = nameRow.createCell(j);
                            BaseCommonqueryresult baseCommonqueryresult = commonqueryresults.get(j);
                            cell.setCellValue(baseCommonqueryresult.getNAME1());
                        }
                    } else {
                        resultObject.setSuccess(false);
                        resultObject.setMessage("导出失败，请联系管理员确认导出模板是否正确！");
                        return resultObject;
                    }

                    for (int i = 0; i < exportDataList.size(); i++) {
                        Map<String, Object> itemData = exportDataList.get(i);
                        Row row = sheet.createRow(i + 1);
                        if (row != null) {
                            for (int k = 0; k < itemData.keySet().size(); k++) {
                                Cell cell = row.createCell(k);
                                if (StringUtils.isNotBlank(itemData, "field" + k)) {
                                    cell.setCellValue(itemData.get("field" + k).toString());
                                } else {
                                    cell.setCellValue("");
                                }
                            }
                        } else {
                            resultObject.setSuccess(false);
                            resultObject.setMessage("导出失败，请联系管理员确认导出模板是否正确！");
                            return resultObject;
                        }
                    }
                } else {
                    resultObject.setSuccess(false);
                    resultObject.setMessage("导出失败，请联系管理员确认导出模板是否正确！");
                    return resultObject;
                }
            } else {
                resultObject.setSuccess(false);
                resultObject.setMessage("导出失败，请联系管理员确认导出模板是否正确！");
                return resultObject;
            }

            workbook.write(outputStream);
            Map<String, Object> return_data = new HashMap<>();
            return_data.put("tmp_file_name", uuid_file_name);
            resultObject.setData(return_data);

            return resultObject;


        } catch (IOException e) {
            resultObject.setSuccess(false);
            resultObject.setMessage("数据导出失败，请重试！");
            return resultObject;
        }

        //获取导出模版的完整路径
    }

    /**
     * 根据前端传入的sql，查询编码，条件查询所有参数
     *
     * @param param
     * @return
     */
    private List<Map<String, Object>> getAllDataByCondition(Map<String, Object> param, HttpSession session) {
        MapTrimUtil.MapValueTrim(param);
        //判断sql存不存在
        if (StringUtils.isBlank(param, "sql")) {
            return null;
        }
        String sql = param.get("sql").toString();
        sql=sql.replaceAll(";","");
        LoginUser loginUser = (LoginUser) session.getAttribute(ResourceRepository.SESSION_LOGIN_USER);
        if (loginUser == null) {
            loginUser = new LoginUser();
        }
        sql = replaceByParam(sql, loginUser);
        String codeId = param.get("codeId").toString();
        List<Map<String, Object>> mapList = baseCommonqueryMapper.getConditionsByCode(codeId);
        if (!(mapList != null && mapList.size() > 0)) {
            return null;
        }
        //获取结果集合
        List<Map<String, Object>> resultList = baseCommonqueryMapper.getResultsByCode(codeId);
        if (!(resultList != null && resultList.size() > 0)) {
            return null;
        }
        //遍历条件集合，将查询条件中有的条件替换成sql语句部分
        if (mapList != null) {
            for (Map<String, Object> map : mapList) {
//                if (StringUtils.isNotBlank(map, "REPLACEFIELD")) {
//                    String replacefield = map.get("REPLACEFIELD").toString();
//                    if (StringUtils.isNotBlank(param, replacefield)) {
//                        String str = param.get(replacefield).toString();
//                        sql = sql.replace(replacefield, str);
//                    } else {
//                        sql = sql.replace(replacefield, "");
//                    }
//                }
                if (StringUtils.isNotBlank(map, "REPLACEFIELD")) {
                    String replacefield = map.get("REPLACEFIELD").toString();
                    boolean dataFlag = false;//用于判断是不是日期输入框
                    if (StringUtils.isNotBlank(map, "COMPONENTNAME") && ("5".equals(map.get("COMPONENTNAME")) || "6".equals(map.get("COMPONENTNAME")))) {
                        dataFlag = true;
                    }
                    if (StringUtils.isNotBlank(param, replacefield)) {
                        String str = param.get(replacefield).toString();
                        sql = sql.replace(replacefield, str);
                    } else {
                        if (dataFlag) {
                            String reverseSql = new StringBuilder(sql).reverse().toString();
                            String reverseCurr = new StringBuilder(replacefield).reverse().toString();
                            int currIndex = reverseSql.indexOf(reverseCurr);
                            String upCaseSql = reverseSql.toUpperCase();
                            int whereIndex = upCaseSql.indexOf("EREHW", currIndex);
                            int firstAndIndex = upCaseSql.indexOf("DNA", currIndex);
                            if (firstAndIndex == -1 || firstAndIndex > whereIndex) {
                                //替换where 和替换符之间的字符串
                                String restr = reverseSql.substring(currIndex +1+ reverseCurr.length(), whereIndex);
                                reverseSql = reverseSql.replace(reverseCurr, "1");
                                reverseSql = reverseSql.replace(restr, "=1 ");
                            } else {
                                //替换and 和替换符之间的字符串
                                String restr = reverseSql.substring(currIndex +1+ reverseCurr.length(), firstAndIndex);
                                reverseSql = reverseSql.replace(reverseCurr, "1");
                                reverseSql = reverseSql.replace(restr, "=1 ");
                            }
                            sql = new StringBuilder(reverseSql).reverse().toString();
                        } else {
                            sql = sql.replace(replacefield, "");
                        }
                    }
                }


            }
        }
        //用于存储返回的结果集合
        List<Map<String, Object>> list = new ArrayList<>();
        Connection connection = getConnection(session);
        Statement state = null;
        String[] splitSql = sql.split("@SPLITSQL");
        String callSql="";
        String selectSql=sql;
        if(splitSql!=null&&splitSql.length>1){
            callSql=splitSql[0];
            selectSql=splitSql[1];
        }
        if(StringUtils.isNotBlank(callSql)){
            if(!callStatement(connection,callSql)){
                return list;
            }
        }
        try {
            state = connection.createStatement();
            ResultSet resultSet = state.executeQuery(selectSql);
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < resultList.size(); i++) {
                    map.put("field" + i, resultSet.getObject(i + 1));

                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (state != null) {
                try {
                    state.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    /**
     * 查询所有的结果ji
     *
     * @param codeId
     * @return
     */
    private List<BaseCommonqueryresult> getResultList(String codeId) {
        Map<String, Object> param = new HashMap<>();
        BaseCommonquery baseCommonquery = baseCommonqueryMapper.selectByCode(codeId);
        param.put("treeNodeId", baseCommonquery.getID());
        List<BaseCommonqueryresult> resultList = baseCommonqueryresultMapper.selectByParentId(param);
        return resultList;
    }

    public ResultObject checkIsExist(Map<String, Object> param) {
        ResultObject resultObject = ResultObject.createInstance();
        if (StringUtils.isBlank(param, "CODE")) {
            resultObject.setSuccess(false);
            resultObject.setMessage("查询编码为空");
        } else {
            String id = null;
            if (StringUtils.isNotBlank(param, "ID")) {
                id = param.get("ID").toString();
            }
            Integer integer = baseCommonqueryMapper.selectCountByCode(param.get("CODE").toString(), id);
            if (integer != null && integer > 0) {
                resultObject.setSuccess(false);
                resultObject.setMessage("该编码已经存在");
            } else {
                resultObject.setSuccess(true);
                resultObject.setMessage("");
            }
        }
        return resultObject;
    }

    public ResultObject deleteCommonquery(BaseCommonquery baseCommonquery) {
        ResultObject resultObject = ResultObject.createInstance();
        if(baseCommonquery.getID()==null){
            return resultObject;
        }
        List<String>list=new ArrayList<>();
        addId(list,baseCommonquery.getID().toString());
        baseCommonqueryMapper.deleteByList(list);
        baseCommonqueryconditionMapper.deleteContitionByHeadId(list);
        baseCommonqueryresultMapper.deleteResultByHeadId(list);
        return resultObject;
    }
    public List<String> addId(List<String> list,String pid){
        list.add(pid);
        List<BaseCommonquery> baseCommonqueries = baseCommonqueryMapper.selectNodeByParentid(pid);
        if(baseCommonqueries!=null&&baseCommonqueries.size()>0){
            for(BaseCommonquery baseCommonquery:baseCommonqueries){
                addId(list,baseCommonquery.getID().toString());
            }
        }
        return list;

    }

    public ResultObject deleteCondition(BaseCommonquerycondition baseCommonquerycondition) {
        baseCommonqueryconditionMapper.deleteByPrimaryKey(baseCommonquerycondition);
        return ResultObject.createInstance();
    }

    public ResultObject deleteResult(BaseCommonqueryresult baseCommonqueryresult) {
        baseCommonqueryresultMapper.deleteByPrimaryKey(baseCommonqueryresult);
        return ResultObject.createInstance();
    }

    public PagedObject<List<Map<String, Object>>> selectAllDataByTempleteReview(Map<String, Object> param, int pageNum, int pageSize, int count, HttpSession session) {
        MapTrimUtil.MapValueTrim(param);
        JSONObject jsonObject = JSONObject.fromObject(param);
        String jsonstr = jsonObject.getString("results");
        Gson gJson = new Gson();
        List<Map<String, Object>> resultList = gJson.fromJson(jsonstr, List.class);
        int max = 0;
        if (resultList != null && resultList.size() > 0) {
            for (Map map1 : resultList) {
                if (StringUtils.isNotBlank(map1, "NAME1")) {
                    max++;
                }
            }
        }
        String contio = jsonObject.getString("condition");
        List<Map<String, Object>> conditions = gJson.fromJson(contio, List.class);
        if (StringUtils.isBlank(param, "sql")) {
            return null;
        }
        String sql = param.get("sql").toString();
        LoginUser loginUser = (LoginUser) session.getAttribute(ResourceRepository.SESSION_LOGIN_USER);
        if (loginUser == null) {
            loginUser = new LoginUser();
        }
        sql = replaceByParam(sql, loginUser);
        if (conditions != null && conditions.size() > 0) {
            for (Map<String, Object> key : conditions) {
                if (StringUtils.isBlank(key, "REPLACEFIELD")) {
                    continue;
                }
                if (StringUtils.isNotBlank(param, key.get("REPLACEFIELD"))) {
                    sql = sql.replace(key.get("REPLACEFIELD").toString(), param.get(key.get("REPLACEFIELD")).toString());
                } else {
                    sql = sql.replace(key.get("REPLACEFIELD").toString(), "");
                }
            }
        }
        String countSql = "select count(*) from (" + sql + ") A";
        String limitSql = sql + " limit " + (pageNum - 1) * pageSize + "," + pageNum * pageSize;
        Page<Map<String, Object>> page = PageHelper.startPage(pageNum, pageSize, count < 0);
        List<Map<String, Object>> list = new ArrayList<>();
        Connection connection = getConnection(session);
        Statement state = null;
        Statement state1 = null;
        try {
            state = connection.createStatement();
            state1 = connection.createStatement();
            ResultSet coutSet = state1.executeQuery(countSql);
            while (coutSet.next()) {
                count = coutSet.getInt(1);
            }
            ResultSet resultSet = state.executeQuery(limitSql);
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < max; i++) {
                    map.put("field" + i, resultSet.getObject(i + 1));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (state != null) {
                try {
                    state.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        PagedObject<List<Map<String, Object>>> pagedObject = new PagedObject<List<Map<String, Object>>>(list, count < 0 ? (int) page.getTotal() : count);
        return pagedObject;
    }

    /**
     * 将sql中的常量替换掉
     * CONNNENTUSERNO 用户编号
     * CONNNENTUSERNAME 用户名
     * CONNNENTUSERDEPTNO 部门编号
     * CONNNENTUSERDEPTNAME 部门名称
     * CONNNENTUSERCOMPANYCODE 公司编号
     * CONNNENTUSERCOMPANYNAME 公司名称
     *
     * @param sql
     * @param loginUser
     * @return
     */
    public String replaceByParam(String sql, LoginUser loginUser) {
        return sql.replace("CONNNENTUSERNO", loginUser.getUSERNO())
                .replace("CONNNENTUSERNAME", loginUser.getUSERNAME())
                .replace("CONNNENTUSERCOMPANYCODE", loginUser.getCOMPANY_CODE())
                .replace("CONNNENTUSERCOMPANYNAME", loginUser.getCOMPANY_NAME());
    }

    public ResultObject caculateDate(Map<String, Object> param) {
        ResultObject resultObject= ResultObject.createInstance();
        if(StringUtils.isBlank(param,"MODE")){
            resultObject.setSuccess(false);
            return resultObject;
        }
        int num=0;
        String mode=param.get("MODE").toString();
        Calendar calendar=Calendar.getInstance();
        SimpleDateFormat s=new SimpleDateFormat("yyyy-MM-dd");
        if(StringUtils.isNotBlank(param,"num")){
            num=Integer.parseInt(param.get("num").toString());
        }
        if("Y".equals(mode)){
            calendar.add(Calendar.YEAR,num);
        }
        if("W".equals(mode)){
            calendar.add(Calendar.WEDNESDAY,num);
        }
        if("D".equals(mode)){
            calendar.add(Calendar.DAY_OF_MONTH,num);
        }
        if("M".equals(mode)){
            calendar.add(Calendar.MONTH,num);
        }
        resultObject.setData(s.format(calendar.getTime()));
        resultObject.setSuccess(true);
        return resultObject;
    }

    public ResultObject filterListGoodsTypesForSelect(HttpSession session, Map<String,Object>param) {
        if (StringUtils.isNotBlank(param,"dept")) {

            //存儲所有的大類別
            List<String> pIDs = new ArrayList<>();

            List<BaseDictionary> baseDictionaries = baseDictionaryMapper.selectFilterChildByPid(param.get("dept").toString());

            //最终结果集
            Map<String, Map<String, List<Map<String, String>>>> resultMapList = new LinkedHashMap<>();
            //最终返回的结果集
            List<Map<String, String>> returnList = new LinkedList<>();

            for (BaseDictionary baseDictionary : baseDictionaries) {
                BaseDictionary bigType = baseDictionaryMapper.selectParentByPID(baseDictionary.getPID()).get(0);//取出大类
                BaseDictionary bestType = baseDictionaryMapper.selectParentByPID(bigType.getPID()).get(0);//取出部类
                if (resultMapList.containsKey(bestType.getID())) {
                    Map<String, List<Map<String, String>>> bigTypeMap = resultMapList.get(bestType.getID());
                    if (bigTypeMap.containsKey(bigType.getID())) {
                        List<Map<String, String>> smallTypeMap = bigTypeMap.get(bigType.getID());
                        Map<String, String> map = new LinkedHashMap<>();
                        map.put(baseDictionary.getPARACODE(), baseDictionary.getPARANAME());
                        smallTypeMap.add(map);
                    } else {
                        List<Map<String, String>> list = new LinkedList<>();
                        Map<String, String> map = new LinkedHashMap<>();
                        map.put(baseDictionary.getPARACODE(), baseDictionary.getPARANAME());
                        list.add(map);
                        bigTypeMap.put(bigType.getID(), list);
                    }
                } else {
                    Map<String, List<Map<String, String>>> listMap = new LinkedHashMap<>();
                    List<Map<String, String>> mapList = new LinkedList<>();
                    Map<String, String> stringMap = new LinkedHashMap<>();
                    stringMap.put(baseDictionary.getPARACODE(), baseDictionary.getPARANAME());
                    mapList.add(stringMap);
                    listMap.put(bigType.getID(), mapList);
                    resultMapList.put(bestType.getID(), listMap);
                }
            }

            Object[] bestKeys = resultMapList.keySet().toArray();
            for (Object object : bestKeys) {

                List<Map<String, Object>> bestID = baseDictionaryMapper.selectByID(object.toString());
                Map<String, String> itemMap = new LinkedHashMap<>();
                itemMap.put("id", bestID.get(0).get("PARACODE").toString());
                itemMap.put("word", "-".concat(bestID.get(0).get("PARANAME").toString()));
                returnList.add(itemMap);

                Map<String, List<Map<String, String>>> bigMaps = resultMapList.get(object.toString());

                Object[] bigKeys = bigMaps.keySet().toArray();

                for (Object bigKey : bigKeys) {

                    List<Map<String, Object>> bigID = baseDictionaryMapper.selectByID(bigKey.toString());
                    Map<String, String> bigItemMap = new LinkedHashMap<>();
                    bigItemMap.put("id", bigID.get(0).get("PARACODE").toString());
                    bigItemMap.put("word", "--".concat(bigID.get(0).get("PARANAME").toString()));
                    returnList.add(bigItemMap);
                    if(!"bigType".equals(param.get("type"))){
                        List<Map<String, String>> smallMaps = bigMaps.get(bigKey.toString());
                        for (Map<String, String> map : smallMaps) {

                            Map<String, String> map1 = new LinkedHashMap<>();
                            map1.put("id", map.keySet().toArray()[0].toString());
                            map1.put("word", "----".concat(map.get(map.keySet().toArray()[0].toString())));
                            returnList.add(map1);
                        }
                    }

                }
            }
            return ResultObject.createInstance(true, "", returnList);
        } else {
            return listGoodsTypesForSelect(session,param);
        }
    }
    /**
     * 获取商品分类下拉数据
     *
     * @return ResultObject
     */
    public ResultObject listGoodsTypesForSelect(HttpSession session, Map<String,Object>param) {

        //最终集合
        List<Map<String, Object>> listMap = new LinkedList<>();
        //获取商品类型ID
        List<BaseDictionary> baseDictionaries = baseDictionaryMapper.selectNodeByPcode("COM_TYPE_STRUCTURE");
        //循环第一类
        for (BaseDictionary baseDictionary : baseDictionaries) {
            Map<String, Object> mapItem = new HashMap<>();
            mapItem.put("id", baseDictionary.getPARACODE());
            mapItem.put("word", "-".concat(baseDictionary.getPARANAME()));
            listMap.add(mapItem);
            List<BaseDictionary> baseDs = baseDictionaryMapper.selectChildrenByPid(baseDictionary.getID());
            for (BaseDictionary bd2 : baseDs) {
                Map<String, Object> mapItem2 = new HashMap<>();
                mapItem2.put("id", bd2.getPARACODE());
                mapItem2.put("word", "--".concat(bd2.getPARANAME()));
                listMap.add(mapItem2);
                if(!"bigType".equals(param.get("type"))){
                    List<BaseDictionary> baseBd3 = baseDictionaryMapper.selectChildrenByPid(bd2.getID());
                    for (BaseDictionary bd3 : baseBd3) {
                        Map<String, Object> mapItem3 = new HashMap<>();
                        mapItem3.put("id", bd3.getPARACODE());
                        mapItem3.put("word", "---".concat(bd3.getPARANAME()));
                        listMap.add(mapItem3);
                    }
                }
            }
        }
        return ResultObject.createInstance(true, "", listMap);
    }

    public ResultObject getNameByPageId(String pageId) {
        ResultObject resultObject= ResultObject.createInstance(false);
        String name=baseCommonqueryMapper.getNameByPageId(pageId);
         if(StringUtils.isNotBlank(name)){
             resultObject.setSuccess(true);
             resultObject.setData(name);
        }
        return resultObject;
    }
}
